<?php

class Client_Table_Auction_Lots extends Apk_Db_Table {

    protected $_name = 'auction_lots';
    protected $_primary = 'ID';
    public $_ERROR = "";

    public function GetError() {
        return $this->_ERROR;
    }

    public function getDataList($page = 1, $sort = null, $page_count = null, $filters = array()) {
        $fields = array(
            'id',
            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
            'area',
            'parcel_address' => 'address',
            'purpose'
        );

        $sel = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->where($this->_name . ".is_ghost = ?", 0)
                ->where($this->_name . ".is_removed = ?", 0)
                ->where($this->_name.".id_status = 2 or (". 
                        $this->_name .".id_status > 10 and ".
                        $this->_name .".is_approved = 1) or ". $this->_name .".id_status = 15")
                ->joinLeft(array('auction'), "auction.id = " . $this->_name . ".id_auction", array('start_date'));
        
    	if (!empty($filters)) {
    		foreach ($filters as $k => $v){
    			switch ($k) {
    				/*case 'showAll': //too HARD
    					$sel->joinLeft('dir_region', "substr(LPAD(dir_region.koatuu, 10, 0),0,2) = substr(LPAD(cad_num_koatuu, 10, 0),0,2)", array('region_id' => "dir_region.id"))
			                ->joinLeft('dir_district', "substr(LPAD(dir_district.koatuu, 10, 0),0,5) = substr(LPAD(cad_num_koatuu, 10, 0),0,5) and dir_district.class_level < 3", array('district_id' => "dir_district.id"))
			                ->joinLeft('dir_town', "LPAD(dir_town.koatuu, 10, 0) = LPAD(cad_num_koatuu, 10, 0)", array('town_id' => "dir_town.id"));
    					break;*/ 
    				case 'by_cad_num':
    					$cad_num = preg_replace("/:/u", "", $v);
                		$sel->where("concat(LPad(cad_num_koatuu, 10, 0), LPad(cad_num_zone, 2, 0), LPad(cad_num_quarter, 3, 0), LPad(cad_num_parcel, 4, 0)) like '" . $cad_num . "%'");
    					break;
    				case 'id_auction':
    					if(intval($v)>0) $sel->where("id_auction = ?", intval($v));
    					break;
    				case 'by_id':
	                    if(intval($v) > 0) $sel->where($this->_name.'.id = '.intval($v));
	                    break;
	                case 'by_region':
	                    if(intval($v)>0) {
	                    	$sel->from('dir_region', array())
	                    		->where("dir_region.id = ?", intval($v))
	                    		->where("substr(LPAD(dir_region.koatuu, 10, 0),0,2) = substr(LPAD(cad_num_koatuu, 10, 0),0,2)");
	                    }
	                    break;
	                case 'by_district':
	                    if(intval($v)>0) {
	                    	$sel->from('dir_district', array())
	                    		->where("dir_district.id = ?", intval($v))
	                    		->where("substr(LPAD(dir_district.koatuu, 10, 0),0,5) = substr(LPAD(cad_num_koatuu, 10, 0),0,5) and dir_district.class_level < 3");
	                    }
	                    break;
	                case 'by_town':
	                    if(intval($v)>0) {
	                    	$sel->from('dir_town', array())
	                    		->where("dir_town.id = ?", intval($v))
	                    		->where("LPAD(dir_town.koatuu, 10, 0) = LPAD(cad_num_koatuu, 10, 0)");
	                    }
	                    break;
    			}
    		}
        }
        
        $pieces = preg_split("/\s/u", $sort);
        switch ($pieces[0]) {
            case 'id':
                $sel->order($this->_name.'.id '.$pieces[1]);
                break;
            case 'cad_num':
                $sel->order("concat(cad_num_koatuu, cad_num_zone, cad_num_quarter, cad_num_parcel) " . $pieces[1]);
                break;
            case 'area':
            case 'start_date':
            case 'purpose':
                $sel->order($sort);
                break;
            case 'parcel_address':
                $sel->order("address " . $pieces[1]);
                break;
            default:
                $sel->order("start_date DESC");
                break;
        }
        return $this->setDecorator("lotDecorator")->getPager($sel, $page, array('perPage' => $page_count));
    }

    public function getDataRow($id) {
        $fields = array(
            'id_auction',
            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
            'area',
            'parcel_address' => 'address',
            'purpose',
            'land_auctions_organizer',
            'urban_needs_uses',
            'conditions_of_contract_type',
            'conditions_of_contract_text',
            'start_price',
            'urban_land_conditions',
            'limit_land_development',
            'size_registration_fee',
            'size_of_guarantee_fee',
            'the_order_of_contribution',
            'executor_bank_account',
            'executor_name',
            'executor_address',
            'contact_person_position',
            'contact_person_name',
            'contact_person_phone',
            'add_date' => $this->_name.".add_date",
            'modify_date' => $this->_name.".modify_date",
            'executor_name',
            'executor_address',
            'contact_person_position',
            'contact_person_name',
            'contact_person_phone',
            'price','annual_fee','term_of_use',
            'id_status',
            'is_approved',
            'reject_reason'
        );
        
        $sel = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->joinLeft('auction', $this->_name . ".id_auction = auction.id",array('start_date' => 'auction.start_date'))
                ->joinLeft('auction_address', "auction.id_address = auction_address.id", array('place' => 'auction_address.place'))
                ->joinLeft('dir_region', "dir_region.id = auction_address.id_region", array('region' => "dir_region.name"))
                ->joinLeft('dir_district', "dir_district.id = auction_address.id_district", array('district' => "IF(dir_district.type='М', concat('м. ', dir_district.name), dir_district.name)"))
                ->joinLeft('dir_town', "dir_town.id = auction_address.id_town", array('town' => "dir_town.name"))
                ->joinLeft('dir_town_type', "dir_town.town_type = dir_town_type.id", array('town_type' => "dir_town_type.shortname"))
                ->where($this->_name . ".id = ?", $id);

        return $this->setDecorator("lotDecoratorFull")->getList($sel)->getRow(0)->toArray();
    }

    public function getLotInfo($id) {
        $fields = array(
            'cad_num' => "concat(LPAD(cad_num_koatuu, 10, 0), ':', LPAD(cad_num_zone, 2, 0), ':', LPAD(cad_num_quarter, 3, 0), ':', LPAD(cad_num_parcel, 4, 0))",
            'area',
            'parcel_address' => 'address',
            'purpose',
            'land_auctions_organizer',
            'urban_needs_uses',
            'conditions_of_contract_type',
            'conditions_of_contract_text',
            'start_price',
            'urban_land_conditions',
            'limit_land_development',
            'size_registration_fee',
            'size_of_guarantee_fee',
            'the_order_of_contribution',
            'executor_bank_account',
            'executor_name',
            'executor_address',
            'contact_person_position',
            'contact_person_name',
            'contact_person_phone',
            'add_date' => $this->_name.".add_date",
            'modify_date' => $this->_name.".modify_date"
        );

        $sel = $this->getAdapter()->select()
                ->from($this->_name, $fields)
                ->where($this->_name . ".id = ?", $id);

        return $this->setDecorator("lotDecorator")->getList($sel)->getRow(0)->toArray();
    }

    public function getLotFilesCount($lot_id) {
        $sel = $this->getAdapter()->select()
                ->from("auction_attaches", array('files_count' => "count(1)"))
                ->where("is_removed = ?", 0)
                ->where("id_lot = ?", $lot_id);

        return $this->getList($sel)->getRow(0)->toArray();
    }

    public function getAuctionLotsIDs($auction_id) {
        $sel = $this->getAdapter()->select()
                ->from($this->_name, array('id'))
                ->where("is_removed = ?", 0)
                ->where("id_auction = ?", $auction_id);

        return $this->getList($sel)->toArray();
    }

    protected function lotDecorator($key, $value) {
        switch ($key) {
        	case 'start_date':
    	 		if($value=="" || $value===null) $result[$key] = '';
                else{
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y H:i');
                }
            	break;
            case 'area':
                if (preg_match("/^\./u", $value)) $result[$key] = "0" . $value;
                else $result[$key] = $value;
                break;
            default:
                $result[$key] = $value;
                break;
        }
    

        return $result;
    }
    
    protected function lotDecoratorFull($key, $value) {
        switch ($key) {
        	case 'add_date':
            case 'modify_date':
            case 'start_date':
    	 		if($value=="" || $value===null)$result[$key] = '';
                else{
                    $date = new DateTime($value);
                    $result[$key] = $date->format('d.m.Y H:i');
                }
            	break;
            case 'area':
                if (preg_match("/^\./u", $value)) $result[$key] = "0" . $value;
                else $result[$key] = $value;
                break;
            case 'town':
            case 'region':
            case 'district':
            	$str_prefix = "";
            	$dot_pose = strpos($value, '.');
            	if($dot_pose!==false) {
            		$str_prefix = mb_substr($value,0,$dot_pose+1);
            		$value = mb_substr($value,$dot_pose+2);
            	} 
            	$result[$key] = $str_prefix.mb_convert_case(mb_strtolower(trim($value)), MB_CASE_TITLE, 'UTF-8');
            	break;
            /*case 'region':
                $result[$key] = $value. ' область ';
                break;
            case 'district':
                $result[$key] = $value. ' район ';
                break;
            case 'town':
                $result[$key] = $value. ' ';
                break;*/
            default:
                $result[$key] = $value;
                break;
        }
        return $result;
    }

}
